package com.futvan.z.framework.util;

import java.io.IOException;
import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;

import org.apache.commons.dbcp2.BasicDataSource;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.SqlSessionTemplate;
import org.springframework.core.io.DefaultResourceLoader;
import org.springframework.core.io.Resource;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;

import com.futvan.z.framework.core.z;
import com.futvan.z.system.zdb.z_db;
import com.futvan.z.system.zdb.z_db_table;
import com.futvan.z.system.zdb.z_db_table_column;

/**
 * 数据库工具类
 * @author 42239
 *
 */
public class DBUtil {
	public static void main(String[] args) {
		z_db db = new z_db();
		db.setDbip("192.168.1.147");
		db.setDb_port("1433");
		db.setUsername("sa");
		db.setPassword("Merro1234");
		db.setDriverClassName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
		db.setDb_type("sqlserver");
		db.setDb_name("sa");
		db.setUrl("jdbc:sqlserver://"+db.getDbip()+":"+db.getDb_port()+";DatabaseName="+db.getDb_name());
		List<z_db_table> tables = getTables(db);
		if(tables.size()<1) {
			System.out.println("未获取到表信息");
		}
		for (z_db_table t : tables) {
			System.out.println(t.getTable_id());
			List<z_db_table_column> columns = t.getZ_db_table_column_list();
			for (z_db_table_column c : columns) {
				System.out.println(c.getColumn_id());
			}
		}
	}

	/**
	 * 获取数据库连接
	 * @param db
	 * @return
	 */
	public static Connection getConnection(z_db db) {
		Connection conn = null;
		try {
			if(z.isNotNull(db) 
					&& z.isNotNull(db.getUrl()) 
					&& z.isNotNull(db.getUsername()) 
					&& z.isNotNull(db.getPassword()) 
					&& z.isNotNull(db.getDriverClassName())) {
				Class.forName(db.getDriverClassName()).newInstance();

				Properties info = new Properties();
				info.put("user", db.getUsername());
				info.put("password", db.getPassword());
				//设置为管理员身份
				//info.put("internal_logon", "sysdba");
				conn = DriverManager.getConnection(db.getUrl(), info);
				if(!conn.isValid(0)) {
					conn.close();
					z.Error("获取数据库连接Connection出错|Connection未正常连接");
				}
			}
		} catch (Exception e) {
			z.Error("获取数据库连接Connection出错", e);
		}
		return conn;
	}

	/**
	 * 获取所有表信息
	 * @param conn
	 * @return
	 */
	public static List<z_db_table> getTables(z_db db){
		List<z_db_table> tables = new ArrayList<z_db_table>();
		if(z.isNotNull(db)) {
			Connection conn = getConnection(db);
			try {
				//获取数据库的元数据
				DatabaseMetaData dbMetaData = conn.getMetaData();
				ResultSet rs = null;
				String schemaPattern = null;
				String Catalog = null;
				if("oracle".equals(db.getDb_type())) {
					schemaPattern = db.getUsername().toUpperCase();
					Catalog = conn.getCatalog();
				}else if("mysql".equals(db.getDb_type())) {
					schemaPattern = db.getDb_name().toUpperCase();
					Catalog = conn.getCatalog();
				}else if("sqlserver".equals(db.getDb_type())) {
					//schemaPattern = db.getDb_name().toUpperCase();
					Catalog = conn.getCatalog();
				}
				rs = dbMetaData.getTables(Catalog, schemaPattern, null,new String[] {"TABLE"});
				while(rs.next()){
					//获取表名
					z_db_table t = new z_db_table();
					String tableName = rs.getString("TABLE_NAME");
					
					//jdbc查SQLServer数据库会多出两大个表
					if("sqlserver".equals(db.getDb_type()) ) {
						if("trace_xe_action_map".equals(tableName) || "trace_xe_event_map".equals(tableName)) {
							continue;
						}
					}
					
					t.setZid(z.newZid("z_db_table"));
					t.setPid(db.getZid());
					t.setTable_id(tableName);
					t.setTable_title(tableName);
					//主键字段
					ResultSet pkInfo = dbMetaData.getPrimaryKeys(db.getDb_name().toUpperCase(), null, tableName);
					while (pkInfo.next()){
						//获取主键字段名
						t.setTable_pk(pkInfo.getString("COLUMN_NAME"));
					}
					//根据表名获取字段信息
					String sql = "select * from " + tableName;
					PreparedStatement ps = conn.prepareStatement(sql);
					ResultSet rstable = ps.executeQuery();
					//结果集元数据
					ResultSetMetaData meta = rstable.getMetaData();
					//表列数量
					int columeCount=meta.getColumnCount();
					List<z_db_table_column> tclist = new ArrayList<z_db_table_column>();
					for (int i=1;i<=columeCount;i++){
						z_db_table_column tc = new z_db_table_column();
						tc.setZid(z.newZid("z_db_table_column"));
						tc.setPid(t.getZid());
						//字段名称
						String columnName = meta.getColumnName(i);
						tc.setColumn_id(columnName);
						//字段标题
						String columnLabel = meta.getColumnLabel(i);
						tc.setColumn_name(columnLabel);
						//字段类型
						String columnType = meta.getColumnTypeName(i);
						tc.setColumn_type(columnType);
						//是否非空
						int columnIsNull = meta.isNullable(i);
						tc.setIs_null(columnIsNull+"");
						//字段长度
						int precision = meta.getPrecision(i);
						//小数点精度
						int scale = meta.getScale(i);
						if(scale>0) {
							tc.setColumn_length(precision+","+scale);
						}else {
							tc.setColumn_length(precision+"");
						}
						tclist.add(tc);
					}
					t.setZ_db_table_column_list(tclist);
					tables.add(t);
				}
			} catch (SQLException e) {
				z.Error("获取库数据库结构信息出错", e);
			}
			try {
				conn.close();
			} catch (SQLException e) {
				z.Error("关闭数据库连接Connection出错", e);
			}

		}
		return tables;
	}

	/**
	 * 解析SQL获取字段
	 * @param sql
	 * @param s
	 * @throws Exception 
	 */
	public static z_db_table parseSQL(String sql,SqlSession s) throws Exception {
		z_db_table table = new z_db_table();
		List<z_db_table_column> z_db_table_column_list = new ArrayList<z_db_table_column>();
		if(z.isNotNull(sql) && z.isNotNull(s)) {
			Connection conn = s.getConfiguration().getEnvironment().getDataSource().getConnection();
			Statement st = conn.createStatement();
			ResultSet rs = st.executeQuery(sql);
			ResultSetMetaData sqlTable = rs.getMetaData();
			for (int i = 1; i <=sqlTable.getColumnCount(); i++) {
				z_db_table_column c = new z_db_table_column();
				c.setColumn_id(sqlTable.getColumnLabel(i));
				z_db_table_column_list.add(c);
			}
			table.setTable_id(sqlTable.getTableName(1));
			table.setZ_db_table_column_list(z_db_table_column_list);
			st.close();
			conn.close();
		}
		return table;
	}

	/**
	 * 根据数据库连接信息，创建sqlsession对象
	 * @param db
	 */
	public static void CreateSqlSessionTemplate(z_db db){
		try {
			if(!"z".equals(db.getDbid()) && z.isNotNull(db)) {//去掉主库
				//构建连接参数对象
				BasicDataSource ds = new BasicDataSource();
				//驱动
				ds.setDriverClassName(db.getDriverClassName());
				//连接URL
				ds.setUrl(db.getUrl());
				//连接用户名
				ds.setUsername(db.getUsername());
				//连接密码
				ds.setPassword(db.getPassword());
				//初始连接数
				BigDecimal initialsize = new BigDecimal(db.getInitialSize());
				if(initialsize.compareTo(new BigDecimal(0))>0) {
					ds.setInitialSize(initialsize.intValue());
				}else {
					ds.setInitialSize(5);
				}
				//最大空闲
				BigDecimal maxIdle = new BigDecimal(db.getMaxIdle());
				if(maxIdle.compareTo(new BigDecimal(0))>0) {
					ds.setMaxIdle(maxIdle.intValue());
				}else {
					ds.setMaxIdle(10);
				}

				//连接超时时间
				BigDecimal maxWaitMillis = new BigDecimal(db.getMaxWaitMillis());
				if(maxWaitMillis.compareTo(new BigDecimal(0))>0) {
					ds.setMaxWaitMillis(maxWaitMillis.intValue());
				}else {
					ds.setMaxWaitMillis(-1);
				}

				//构建工厂
				SqlSessionFactoryBean sf = new SqlSessionFactoryBean();
				sf.setDataSource(ds);
				Resource[] resources = new PathMatchingResourcePatternResolver().getResources("classpath*:com/futvan/z/**/*SQL.xml");
				sf.setMapperLocations(resources);
				sf.setConfigLocation(new DefaultResourceLoader().getResource("classpath:mybatis.xml"));
				SqlSessionFactory sessionFactory = sf.getObject();

				//获取链接
				SqlSessionTemplate ss = new SqlSessionTemplate(sessionFactory);
				z.Log("连接数据库【"+db.getName()+"】：成功");
				z.dbs.put(db.getDbid(), ss);
			}
		} catch (Exception e) {
			z.Error("连接其它数据库失败|CreateSqlSessionTemplate|"+db.toString(), e);
		}
	}
}
